CREATE FUNCTION fn_asi_SoftCreditCMDM_Amount
        (@pTransNumber int,
        @pOrignatingActivitySeqn int,
        @pSoftCreditId as varchar(50))
  RETURNS money
  AS
  BEGIN
       /*  JB 19-May-06
       Returns a correction value to be applied to an original Soft Credit value.
       This correction value is a total of all the CM/DM that have been issued against
       the Soft Credit
       The original soft credit is identified in Trans_SoftCredit by
       the combination of TRANS_NUMBER and ORIGINATING_ACTIVITY_SEQN.
       Subsequent CM/DM will have the same ORIGINATING_ACTIVITY_SEQN
       but different TRANS_NUMBER
       */
       declare @correctionVal money
       -- get the correction value
       select
            @correctionVal = sum(AMOUNT)
       from
            Trans_SoftCredit
       where
            ORIGINATING_ACTIVITY_SEQN = @pOrignatingActivitySeqn
            and TRANS_NUMBER <> @pTransNumber
            and SOFT_CREDIT_ID=@pSoftCreditId
       return isnull(@correctionVal,0)
  END

GO
GRANT EXECUTE ON  [dbo].[fn_asi_SoftCreditCMDM_Amount] TO [IMIS]
GO